package com.camp.android.trip.n.track;

import java.util.ArrayList;
import java.util.Calendar;

import com.camp.android.trip.n.track.R;
import com.camp.android.trip.n.track.details.Location;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseManager {
	private static final String DATABASE_NAME = "trips.db";

	private static final String TABLE_TRIPS = "trips";
	private static final String TABLE_ITEMS = "items";
	private static final String TABLE_TRIP_DETAILS = "trip_details";
	private static final String TABLE_ITEM_DETAILS = "item_details";

	private static final String COLUMN_TRIP_ID = "trip_id";
	private static final String COLUMN_TRIP_NAME = "trip_name";
	private static final String COLUMN_TRIP_CITY = "trip_city";
	private static final String COLUMN_TRIP_START = "trip_start";
	private static final String COLUMN_TRIP_END = "trip_end";
	private static final String COLUMN_TRIP_RATING = "trip_rating";

	private static final String COLUMN_ITEM_ID = "item_id";
	private static final String COLUMN_ITEM_NAME = "item_name";
	private static final String COLUMN_ITEM_START = "item_start";
	private static final String COLUMN_ITEM_END = "item_end";
	private static final String COLUMN_ITEM_DONE = "item_done";
	private static final String COLUMN_ITEM_RATING = "item_rating";

	private static final String COLUMN_TRIP_DETAILS_ID = "trip_details_id";
	private static final String COLUMN_TRIP_FILE = "trip_file";

	private static final String COLUMN_ITEM_DETAILS_ID = "item_details_id";
	private static final String COLUMN_ITEM_FILE = "item_file";
	private static final String COLUMN_ITEM_DETAIL_TYPE = "item_detail_type";

	private SQLiteDatabase db;
	Context context;

	public DatabaseManager(Context context) {
		this.context = context;

		// create or open the database
		DatabaseHelper helper = new DatabaseHelper(context, "", null, 1);
		this.db = helper.getWritableDatabase();
	}

	private class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, DATABASE_NAME, null, 1);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE " + TABLE_TRIPS + " (" + COLUMN_TRIP_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_TRIP_NAME
					+ " TEXT , " + COLUMN_TRIP_CITY + " TEXT , "
					+ COLUMN_TRIP_START + " TEXT , " + COLUMN_TRIP_END
					+ " TEXT, " + COLUMN_TRIP_RATING + " FLOAT)");

			db.execSQL("CREATE TABLE " + TABLE_ITEMS + " (" + COLUMN_ITEM_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_ITEM_NAME
					+ " TEXT , " + COLUMN_ITEM_START + " TEXT , "
					+ COLUMN_ITEM_END + " TEXT," + COLUMN_ITEM_DONE
					+ " INTEGER, " + COLUMN_ITEM_RATING + " FLOAT, "
					+ COLUMN_TRIP_ID + " INTEGER, " + " FOREIGN KEY ("
					+ COLUMN_TRIP_ID + ") REFERENCES " + TABLE_TRIPS + " ("
					+ COLUMN_TRIP_ID + "))");

			db.execSQL("CREATE TABLE " + TABLE_TRIP_DETAILS + " ("
					+ COLUMN_TRIP_DETAILS_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_TRIP_FILE
					+ " TEXT , " + COLUMN_TRIP_ID + " INTEGER, "
					+ " FOREIGN KEY (" + COLUMN_TRIP_ID + ") REFERENCES "
					+ TABLE_TRIPS + " (" + COLUMN_TRIP_ID + "))");

			db.execSQL("CREATE TABLE " + TABLE_ITEM_DETAILS + " ("
					+ COLUMN_ITEM_DETAILS_ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_ITEM_FILE
					+ " TEXT , " + COLUMN_ITEM_DETAIL_TYPE + " TEXT , "
					+ COLUMN_ITEM_ID + " INTEGER, " + " FOREIGN KEY ("
					+ COLUMN_ITEM_ID + ") REFERENCES " + TABLE_ITEMS + " ("
					+ COLUMN_ITEM_ID + "))");

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub

		}

	}

	public int addTrip(Trip trip) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_TRIP_NAME, trip.name);
		cv.put(COLUMN_TRIP_CITY, trip.city);
		cv.put(COLUMN_TRIP_START, (Long.toString(trip.start.getTimeInMillis())));
		cv.put(COLUMN_TRIP_END, (Long.toString(trip.end.getTimeInMillis())));
		cv.put(COLUMN_TRIP_RATING, trip.rating);
		return (int) db.insert(TABLE_TRIPS, null, cv);

		// db.close();
	}

	public Trip getTripById(int id) {
		String columns = COLUMN_TRIP_ID + "," + COLUMN_TRIP_NAME + ","
				+ COLUMN_TRIP_CITY + "," + COLUMN_TRIP_START + ","
				+ COLUMN_TRIP_END + "," + COLUMN_TRIP_RATING;
		Cursor c = db.rawQuery("SELECT " + columns + " FROM " + TABLE_TRIPS
				+ " WHERE " + COLUMN_TRIP_ID + "=" + id + ";", null);

		c.moveToFirst();
		Calendar start = Calendar.getInstance();
		Calendar end = Calendar.getInstance(); // TODO more
		String startSeconds = c.getString(3);
		String endSeconds = c.getString(4);
		start.setTimeInMillis(Long.parseLong(startSeconds));
		end.setTimeInMillis(Long.parseLong(endSeconds));

		Trip trip = new Trip(c.getInt(0), c.getString(1), c.getString(2),
				start, end, c.getFloat(5));
		return trip;
	}

	public ArrayList<Trip> getTrips() {
		String[] columns = new String[] { COLUMN_TRIP_ID, COLUMN_TRIP_NAME,
				COLUMN_TRIP_CITY, COLUMN_TRIP_START, COLUMN_TRIP_END,
				COLUMN_TRIP_RATING };
		Cursor c = db.query(TABLE_TRIPS, columns, null, null, null, null, null);

		c.moveToFirst();

		ArrayList<Trip> list = new ArrayList<Trip>();

		if (!c.isAfterLast()) {
			do {
				Calendar start = Calendar.getInstance();
				Calendar end = Calendar.getInstance(); // TODO more
				String startSeconds = c.getString(3);
				String endSeconds = c.getString(4);
				start.setTimeInMillis(Long.parseLong(startSeconds));
				end.setTimeInMillis(Long.parseLong(endSeconds));
				Trip trip = new Trip(c.getInt(0), c.getString(1),
						c.getString(2), start, end, c.getFloat(5));
				list.add(trip);

			}
			// move the cursor's pointer up one position.
			while (c.moveToNext());
		}

		return list;
	}

	public ArrayList<Trip> getPastTrips() {
		ArrayList<Trip> list = getTrips();
		ArrayList<Trip> result = new ArrayList<Trip>();
		for (int i = 0; i < list.size(); i++) {
			Trip t = list.get(i);
			if (t.start.getTimeInMillis() < System.currentTimeMillis()) {
				result.add(t);
			}
		}
		return result;
	}

	public ArrayList<Trip> getFutureTrips() {
		ArrayList<Trip> list = getTrips();
		ArrayList<Trip> result = new ArrayList<Trip>();
		for (int i = 0; i < list.size(); i++) {
			Trip t = list.get(i);
			if (t.start.getTimeInMillis() > System.currentTimeMillis()) {
				result.add(t);
			}
		}
		return result;
	}
	
	public Trip getCurrentTrip() {
		ArrayList<Trip> list = getTrips();
		for (int i = 0; i < list.size(); i++) {
			Trip t = list.get(i);
			if ((t.start.getTimeInMillis() < System.currentTimeMillis()) && (t.end.getTimeInMillis() > System.currentTimeMillis())) {
				return t;
			}
		}
		return null;
	}

	public void updateTrip(Trip trip) {
		Cursor c = db.rawQuery(
				"UPDATE " + TABLE_TRIPS + " SET " + COLUMN_TRIP_NAME + "='"
						+ trip.name + "' , " + COLUMN_TRIP_START + "='"
						+ (Long.toString(trip.start.getTimeInMillis()))
						+ "' , " + COLUMN_TRIP_END + "='"
						+ (Long.toString(trip.end.getTimeInMillis())) + "' , "
						+ COLUMN_TRIP_CITY + "='" + trip.city
						+ "' , " + COLUMN_TRIP_RATING + "=" + trip.rating
						+ " WHERE " + COLUMN_TRIP_ID + "=" + trip.id + ";",
				null);
		c.moveToFirst();
		c.close();
	}

	public void deleteTrip(int id) {
		db.delete(TABLE_TRIPS, COLUMN_TRIP_ID+"=?",new String[]{ Integer.toString(id)});
	}

	public int addItem(int tripId, Item item) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ITEM_NAME, item.name);
		cv.put(COLUMN_ITEM_START, (Long.toString(item.start.getTimeInMillis())));
		cv.put(COLUMN_ITEM_END, (Long.toString(item.end.getTimeInMillis())));
		int doneint;
		if (item.done) {
			doneint = 1;
		} else {
			doneint = 0;
		}
		cv.put(COLUMN_ITEM_DONE, doneint);
		cv.put(COLUMN_ITEM_RATING, item.rating);
		cv.put(COLUMN_TRIP_ID, tripId);
		return (int) db.insert(TABLE_ITEMS, null, cv);
	}

	public Item getItem(int id) {
		String columns = COLUMN_ITEM_ID + "," + COLUMN_ITEM_NAME + ","
				+ COLUMN_ITEM_START + "," + COLUMN_ITEM_END + ","
				+ COLUMN_ITEM_DONE + "," + COLUMN_ITEM_RATING;
		Cursor c = db.rawQuery("SELECT " + columns + " FROM " + TABLE_ITEMS
				+ " WHERE " + COLUMN_ITEM_ID + "=" + id + ";", null);

		c.moveToFirst();
		Calendar start = Calendar.getInstance();
		Calendar end = Calendar.getInstance(); // TODO more
		String startSeconds = c.getString(2);
		String endSeconds = c.getString(3);
		start.setTimeInMillis(Long.parseLong(startSeconds));
		end.setTimeInMillis(Long.parseLong(endSeconds));
		int done = c.getInt(4);
		boolean donebool;
		if (done == 1) {
			donebool = true;
		} else {
			donebool = false;
		}
		Item item = new Item(id, c.getString(1), start, end, donebool,
				c.getFloat(5));
		return item;
	}

	public ArrayList<Item> getItems(int tripId) {
		ArrayList<Item> list = new ArrayList<Item>();

		String columns = COLUMN_ITEM_ID + "," + COLUMN_ITEM_NAME + ","
				+ COLUMN_ITEM_START + "," + COLUMN_ITEM_END + ","
				+ COLUMN_ITEM_DONE + "," + COLUMN_ITEM_RATING;
		Cursor c = db.rawQuery("SELECT " + columns + " FROM " + TABLE_ITEMS
				+ " WHERE " + COLUMN_TRIP_ID + "=" + tripId + ";", null);

		c.moveToFirst();

		if (!c.isAfterLast()) {
			do {
				Calendar start = Calendar.getInstance();
				Calendar end = Calendar.getInstance(); // TODO more
				String startSeconds = c.getString(2);
				String endSeconds = c.getString(3);
				start.setTimeInMillis(Long.parseLong(startSeconds));
				end.setTimeInMillis(Long.parseLong(endSeconds));
				int done = c.getInt(4);
				boolean donebool;
				if (done == 1) {
					donebool = true;
				} else {
					donebool = false;
				}

				Item item = new Item(c.getInt(0), c.getString(1), start, end,
						donebool, c.getFloat(5));
				list.add(item);
			}
			// move the cursor's pointer up one position.
			while (c.moveToNext());
		}
		return list;
	}

	public void updateItem(Item item) {
		int doneint;
		if (item.done) {
			doneint = 1;
		} else {
			doneint = 0;
		}
		Cursor c = db.rawQuery(
				"UPDATE " + TABLE_ITEMS + " SET " + COLUMN_ITEM_NAME + "='"
						+ item.name + "' , " + COLUMN_ITEM_START + "='"
						+ (Long.toString(item.start.getTimeInMillis()))
						+ "' , " + COLUMN_ITEM_END + "='"
						+ (Long.toString(item.end.getTimeInMillis())) + "' , "
						+ COLUMN_ITEM_DONE + "='" + doneint
						+ "' , " + COLUMN_ITEM_RATING + "=" + item.rating
						+ " WHERE " + COLUMN_ITEM_ID + "=" + item.id + ";",
				null);
		c.moveToFirst();
		c.close();

	}

	public int getTripId(Item item) {
		String columns = COLUMN_TRIP_ID;
		Cursor c = db.rawQuery("SELECT " + columns + " FROM " + TABLE_ITEMS
				+ " WHERE " + COLUMN_ITEM_ID + "=" + item.id + ";", null);

		c.moveToFirst();
		return c.getInt(0);
	}

	public void deleteItem(int id) {
		// TODO
	}

	public int addTripObject(int tripId, Object detail) {
		// TODO
		return 0;
	}

	public Object getTripObject(int fileId) {
		// TODO
		Object detail = null;
		return detail;
	}

	public ArrayList<Object> getTripObjects(int Tripid) {
		// TODO
		ArrayList<Object> details = null;
		return details;
	}

	public void updateTripObject(int id) {
		// TODO
	}

	public void deleteTripObject(int id) {
		// TODO
	}

	public int addItemDetail(int itemId, Object detail) {
		if (detail.getClass() == Location.class) {
			Location location = (Location) detail;
			ContentValues cv = new ContentValues();
			cv.put(COLUMN_ITEM_FILE, location.location);
			cv.put(COLUMN_ITEM_ID, itemId);
			cv.put(COLUMN_ITEM_DETAIL_TYPE, "Location");
			return (int) db.insert(TABLE_ITEM_DETAILS, null, cv);
		} else {
			return 0;
		}

	}

	public Object getItemDetail(int detailid) {
		String columns = COLUMN_ITEM_FILE + "," + COLUMN_ITEM_DETAIL_TYPE;
		Cursor c = db.rawQuery("SELECT " + columns + " FROM "
				+ TABLE_ITEM_DETAILS + " WHERE " + COLUMN_ITEM_DETAILS_ID + "="
				+ detailid + ";", null);

		c.moveToFirst();
		String type = c.getString(1);
		if (type.equals("Location")) {
			return new Location(detailid, c.getString(0));
		} else {
			return new Object();
		}
	}

	public ArrayList<Object> getItemObjects(int Itemid) {
		// TODO
		ArrayList<Object> details = null;
		return details;
	}

	public void updateItemObjects(int id) {
		// TODO
	}

	public void deleteItemObject(int id) {
		// TODO
	}

}
